import java.sql.ResultSet;
import java.sql.SQLException;


public class StudentModel{

	/**
	 * Adds a student to the system
	 * 
	 * @param uid   Univ. ID of the student
	 * @param first First name of the student
	 * @param last  Last name of the student
	 * @param email Email of the student
	 * @return      Returns true if insert succeeded, false on failure
	 */
	public static boolean addStudent( int uid, String first, String last, String email ){
//		String query = "INSERT INTO Person (`uid`, `first`, `last`, `email`) VALUES ('" + uid + "', '" + first + "', '" + last + "', '" + email + "')";
//		return DB.Update( query );
		return DB.SafeUpdate("INSERT INTO Person (`uid`, `first`, `last`, `email`) VALUES (?, ?, ?, ?)", new String[]{"" + uid, first, last, email} );
	}
	
	/**
	 * Gets the list of students in the system
	 * 
	 * @return Array of students
	 */
	public static StudentInfo[] getStudents(){
		return getStudents( true );
	}
	
	/**
	 * Gets the list of students in the system
	 * 
	 * @param first_last
	 *            True to order by first name, false to order by last name
	 * @return Array of student names
	 */
	public static StudentInfo[] getStudents( boolean first_last ){
		String query = "SELECT uid, first, last, email FROM Person ORDER BY " + ( first_last ? "first, last" : "last, first" );
		ResultSet rs = DB.Query( query );
		StudentInfo users[] = null;
		
		if( rs != null ){
			try{
				// Get row count to create user array
				rs.last();
				users = new StudentInfo[rs.getRow()];
				rs.first();
				
				// Store users into array
				do{
					StudentInfo temp = new StudentInfo();
					temp.email = rs.getString( "email" );
					temp.first = rs.getString( "first" );
					temp.last = rs.getString( "last" );
					temp.uid = Integer.parseInt( rs.getString( "uid" ) );
					users[rs.getRow() - 1] = temp;
				}while( rs.next() );
			}catch( SQLException e ){
				users = null;
			}
		}
		
		return users;
	}
	
	/**
	 * Removes a student from the system
	 * 
	 * @param uid Univ. ID of the student
	 * @return    Returns true on success, false on failure
	 */
	public static boolean removeStudent( int uid ){
		return DB.Transaction( new String[]{ "DELETE FROM Member_Of WHERE `uid`=" + uid, "DELETE FROM Person WHERE `uid`=" + uid } );
	}
	
	/**
	 * Gets in depth information for the student with matching uid
	 * 
	 * @param uid
	 *            Student's UID
	 * @return StudentInfo object with specified students id number, or null if
	 *         they didn't exist
	 * @throws SQLException
	 */
	public static StudentInfo getStudentInfo( int uid ){
		String query = "SELECT * FROM Person WHERE uid=" + uid;
		ResultSet rs = DB.Query( query );
		
		StudentInfo inf = null;
		if( rs != null ){
			try{
				if( rs != null && rs.first() ){
					inf = new StudentInfo();
					inf.first = rs.getString( "first" );
					inf.last = rs.getString( "last" );
					inf.email = rs.getString( "email" );
					inf.uid = Integer.parseInt( rs.getString( "uid" ) );
				}
			}catch( SQLException e ){
				inf = null;
			}
		}
		
		return inf;
	}
}
